create table k3r_logTypes (
	[id]			int identity primary key,
	[name]			nvarchar(50) not null unique
);

go

create table k3r_logs (
	[id]			int identity primary key,
	[type]			int not null foreign key references k3r_logTypes([id]),
	[source]		nvarchar(50) not null,
	[message]		nvarchar(max) not null,
	[number]		int null,
	[ts]			datetime default getdate()
);

go

create proc k3r_addLogType
	@name			nvarchar(50),
	@id				int = null output
as
insert into [k3r_logTypes] ([name])
values (@name);

select @id = scope_identity();

go

create proc k3r_addLog
	@type			int,
	@source			nvarchar(50),
	@message		nvarchar(max),
	@number			int = null,
	@id				int = null output
as
insert into [k3r_logs] ([type], [source], [message], [number])
values (@type, @source, @message, @number);

select @id = scope_identity();


go

create proc k3r_listLog
as
select l.id					,
		l.[type]			,
		lt.[name]			[type_name],
		l.[source]			,
		l.[message]			,
		l.[number]			,
		l.[ts]				[time_stamp]
from k3r_logs l
inner join k3r_logTypes lt
	on l.[type] = lt.[id];

go

create proc k3r_latestLogs
	@type			int = null,
	@source			nvarchar(50) = null
as
select top 100 l.id					,
		l.[type]			,
		lt.[name]			[type_name],
		l.[source]			,
		l.[message]			,
		l.[number]			,
		l.[ts]				[time_stamp]
from k3r_logs l
inner join k3r_logTypes lt
	on l.[type] = lt.[id]
where l.[type] = isnull(@type, l.[type])
	and l.[source] = isnull(@source, l.[source])
order by l.id desc;

go

create proc k3r_printLogMessage 5211
	@id				int
as
declare @message nvarchar(max);

select @message = [message]
from k3r_logs
where [id] = @id

print @message

go

declare @type int;

exec k3r_addLogType 'Test', @type output;
exec k3r_addLogType 'Information';
exec k3r_addLogType 'Warning';
exec k3r_addLogType 'Error';

exec k3r_addLog 4, 'Test', 'Test Message';
exec k3r_listLog;
exec k3r_printLogMessage 1000;
		
create trigger [dbo].[EmailError] 
   ON  [dbo].[k3r_logs] 
   AFTER INSERT, UPDATE
AS 
BEGIN
if (exists (select null
			from inserted
			where type = 4))
	exec msdb.dbo.sp_send_dbmail
		@profile_name = 'Ink Creations',
		@recipients = 'tony@inkcreations.co.uk;richard@dogma.co.uk;richard@inkcreations.co.uk;helen@inkcreations.co.uk',
		@body = '...',
		@subject = 'An error has occured on The High Point Village';
END